﻿using System.Data;
using System.Data.SqlClient;

namespace DataManager
{

    /// <summary>
    /// Summary description for DAL.
    /// </summary>
    public class MsSqlUtils
    {
        protected static bool Connected;
        protected static bool ConnectionCreated;

        //protected string m_ConnectionString = "Data Source=tcp:127.0.0.1,880;Initial Catalog=adatbazisom;User=csongi;Password=csongi";
        protected static SqlConnection m_Connection;
        protected string m_ConnectionString = "Data Source=CSONGI-PC\\SQLEXPRESS;Initial Catalog=filmek2;Integrated Security=SSPI";
       // protected string m_ConnectionString = "Server=CSONG!-PC\\SQLEXPRESS;Database=filmek2;Trusted_Connection=True;"; 
        protected SqlDataAdapter dataAdapter;
        
        protected static SqlTransaction transaction = null;


        public MsSqlUtils()
        {
            dataAdapter = new SqlDataAdapter();
        }

        public void beginTransaction()
        {
            OpenConnection();
            transaction = m_Connection.BeginTransaction();
        }

        public void commitTransaction()
        {
            if (transaction != null)
            {
                transaction.Commit();
                transaction = null;
            }
            CloseConnection();
        }

        public void rollbackTransaction()
        {
            if (transaction != null)
            {
                transaction.Rollback();
                transaction = null;
            }
            CloseConnection();
        }

        protected bool IsConnectionCreated()
        {
            return ConnectionCreated;
        }

        protected bool IsConnected()
        {
            return Connected;
        }

        protected SqlConnection GetConnection()
        {
            return m_Connection;
        }

        /// <summary>
        /// Creates a new Database Connection
        /// </summary>
        /// <returns></returns>
        protected bool CreateConnection()
        {
            // Create the Connection if it was not already created.
            if (ConnectionCreated != true)
            {
                try
                {
                    m_Connection = new SqlConnection(m_ConnectionString);
                    m_Connection.Open();
                    ConnectionCreated = true;
                    m_Connection.Close();
                    Connected = false;
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                return true;
            }
        }


        /// <summary>
        /// Open the Connection when the state is not already open.
        /// </summary>
        protected bool OpenConnection()
        {
            // Open the Connection when the state is not already open.
            if (Connected != true)
            {
                try
                {
                    CreateConnection();
                    m_Connection.Open();
                    Connected = true;
                    return true;
                }
                catch
                {
                    return false;
                }

            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// Close the Connection when the connection is open.
        /// </summary>
        internal void CloseConnection()
        {
            if (Connected == true && transaction == null)
            {
                m_Connection.Close();
                Connected = false;
            }
        }

        /// <summary>
        /// Executes a given query, and returns the result in a datareader
        /// </summary>
        /// <param name="sQuery"> The query to be executed </param>
        /// <param name="ErrMess"> Output error message </param>
        /// <returns></returns>
        protected SqlDataReader ExecuteReader(string query, ref string errorMessage)
        {
            try
            {
                OpenConnection();
                SqlCommand cmd = new SqlCommand(query, m_Connection);
                cmd.Transaction = transaction;
                SqlDataReader rdr = cmd.ExecuteReader();
                errorMessage = "OK";
                return rdr;

            }
            catch (SqlException e)
            {
                errorMessage = e.Message;
                CloseConnection();
                return null;
            }
        }
        /// <summary>
        /// Closes the data reader given as a parameter, and also closes the connection
        /// </summary>
        /// <param name="rdr">The SqlDataReader to be closed</param>
        protected void CloseDataReader(SqlDataReader rdr)
        {
            if (rdr != null)
                rdr.Close();
            CloseConnection();
        }


        /// <summary>
        /// Executes a given query, and returns the result in a dataset
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public DataSet FillDataSetFromQuery(string sqlCommand)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(sqlCommand, m_Connection);
                command.Transaction = transaction;
                command.CommandType = System.Data.CommandType.Text;

                dataAdapter.SelectCommand = command;

                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);

                return dataSet;
            }
            finally
            {
                CloseConnection();
            }
        }

        public void ExecuteCommand(string sqlCommand)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(sqlCommand, m_Connection);
                command.Transaction = transaction;
                command.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Executes a stored procedure that doesn't have any otuput parameters.
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <param name="parameterNames">The list of the parameter names</param>
        /// <param name="parameterValues">The list of the parameter values</param>
        /// <returns></returns>
        public void ExecuteStoredProcedure(string StoredProcedureName, params SqlParameter[] parameters)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedureName, m_Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Transaction = transaction;
                command.Parameters.AddRange(parameters);
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.ToString());
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Executes a given stored procedure, and returns the result in a dataset
        /// </summary>
        /// <param name="StoredProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet FillDataSetFromStoredProcedure(string StoredProcedureName, params SqlParameter[] parameters)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedureName, m_Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Transaction = transaction;
                command.Parameters.AddRange(parameters);

                dataAdapter.SelectCommand = command;

                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);

                return dataSet;
            }
            finally
            {
                CloseConnection();
            }
        }

    }

    

}





